create database bbs
on
(
	NAME='bbs',
	FILENAME='D:\temp\bbs.mdf',
	SIZE=5,
	MAXSIZE=50,
	FILEGROWTH=10%
)
log on
(
	NAME='TestDB_log',
	FILENAME='D:\temp\bbs_log.ldf',
	SIZE=5,
	MAXSIZE=50,
	FILEGROWTH=10%

)
go
use bbs
go

create table bbsUsers
(
	UIDa int primary key identity(1,1),
	uName varchar(10) not null,
	uSex varchar(2) not null check(uSex='男' or uSex='女'),
	uAge int not null check(uAge>=15 and uAge<=60),
	uPoint int not null check(uPoint>=0)
)
create table bbsTopic
(
	tID int primary key identity(1,1),
	tUID int foreign key references bbsUSers(UIDa),
	tSID int ,
	tTitle varchar(100) not null,
	tMsg text not null,
	tTime datetime,
	tCount int,
)
create table bbsReply
(
	rID int primary key  identity(1,1),
	rUID int foreign key references bbsUsers(UIDa),
	rTID int foreign key references bbsTopic(tID),
	rMsg text not null,
	rTime datetime,

)
create table bbsSection
(
	SIDaa int primary key identity(1,1),
	sName varchar(10) not null,
	sUid int
)

select *from bbsReply

--alter table bbsSection add SIDaa int primary key identity(1,1)
alter table bbsSection add constraint FK_UIDa foreign key(SUid) references bbsUsers(UIDa)

alter table bbsTopic add constraint FK_SIDa foreign key(tSID) references bbsSection(SIDaa)

select * from bbsUsers
insert into bbsUsers(uName,uSex,uAge,uPoint)
select '小雨点','女',20,0 union
select '逍遥','男',18,4 union
select '七年级生','男',19,2 

select uName,uPoint from bbsUsers

select * from bbsSection
insert into bbsSection(sName,sUid)
select '技术交流',3 union
select '读书世界',1 union
select '生活百科',3 union
select '八卦区',1

insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
select 2,2,'范跑跑','谁是范跑跑',2008-7-8,1 union
select 1,2,'.NET ','与JAVA的区别是什么呀？',2008-9-1,2 union
select 3,2,'今年夏天最流行什么','有谁知道今年夏天最流行',2008-9-10,0

select *from bbsTopic
insert into bbsReply(rUID,rTID,rMsg,rTime)
select 3,1,'与JAVA的区别是程序语言',2008-9-10 union
select 1,2,'有好有坏，不要站在道德的制高点哔赖',2008-9-10 union
select 2,3,'当然是短袖呀',2008-9-10

select *from bbsReply

delete from bbsUsers where uName='逍遥'

alter table bbsSection drop constraint FK_UIDa

alter table bbsTopic drop constraint FK__bbsTopic__tUID__15502E78
alter table bbsReply drop constraint FK__bbsReply__rUID__182C9B23

update bbsUsers set uPoint=10 where UIDa=3

select *from bbsUsers
delete from bbsSection where sName='生活百科'
select *from bbsSection

truncate table bbsReply
select *from bbsReply